package csu.web.mypetstore.persistence.impl;

import csu.web.mypetstore.domain.Account;
import csu.web.mypetstore.persistence.AccountDao;
import csu.web.mypetstore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class AccountDaoImpl implements AccountDao {
    private static final String GET_ACCOUNT_BY_USERNAME = "SELECT" +
            " SIGNON.USERNAME," +
            "ACCOUNT.EMAIL," +
            "ACCOUNT.FIRSTNAME," +
            "ACCOUNT.LASTNAME," +
            "ACCOUNT.STATUS," +
            "ACCOUNT.ADDR1 AS address1," +
            "ACCOUNT.ADDR2 AS address2," +
            "ACCOUNT.CITY," +
            "ACCOUNT.STATE," +
            "ACCOUNT.ZIP," +
            "ACCOUNT.COUNTRY," +
            "ACCOUNT.PHONE," +
            "PROFILE.LANGPREF AS languagePreference," +
            "PROFILE.FAVCATEGORY AS favouriteCategoryId," +
            "PROFILE.MYLISTOPT AS listOption," +
            "PROFILE.BANNEROPT AS bannerOption," +
            "BANNERDATA.BANNERNAME" +
            " FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA" +
            " WHERE ACCOUNT.USERID =?" +
            " AND SIGNON.USERNAME = ACCOUNT.USERID" +
            " AND PROFILE.USERID = ACCOUNT.USERID" +
            " AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
    private static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD = "SELECT" +
            " SIGNON.USERNAME," +
            "ACCOUNT.EMAIL," +
            "ACCOUNT.FIRSTNAME," +
            "ACCOUNT.LASTNAME," +
            "ACCOUNT.STATUS," +
            "ACCOUNT.ADDR1 AS address1," +
            "ACCOUNT.ADDR2 AS address2," +
            "ACCOUNT.CITY," +
            "ACCOUNT.STATE," +
            "ACCOUNT.ZIP," +
            "ACCOUNT.COUNTRY," +
            "ACCOUNT.PHONE," +
            "PROFILE.LANGPREF AS languagePreference," +
            "PROFILE.FAVCATEGORY AS favouriteCategoryId," +
            "PROFILE.MYLISTOPT AS listOption," +
            "PROFILE.BANNEROPT AS bannerOption," +
            "BANNERDATA.BANNERNAME" +
            " FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA" +
            " WHERE ACCOUNT.USERID =?" +
            " AND SIGNON.PASSWORD =?" +
            " AND SIGNON.USERNAME = ACCOUNT.USERID" +
            " AND PROFILE.USERID = ACCOUNT.USERID" +
            " AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
    private static final String UPDATE_ACCOUNT = "UPDATE ACCOUNT SET" +
            " EMAIL =?," +
            "FIRSTNAME =?," +
            "LASTNAME =?," +
            "STATUS =?," +
            "ADDR1 =?," +
            "ADDR2 =?," +
            "CITY =?," +
            "STATE =?," +
            "ZIP =?," +
            "COUNTRY =?," +
            "PHONE =?" +
            "WHERE USERID =?";
    private static final String INSERT_ACCOUNT = "INSERT INTO ACCOUNT" +
            " (EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE, USERID)" +
            " VALUES" +
            " (?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String UPDATE_PROFILE = "UPDATE PROFILE SET" +
            " LANGPREF =?," +
            "FAVCATEGORY =?," +
            "MYLISTOPT =?," +
            "BANNEROPT =?" +
            " WHERE USERID =?";
    private static final String INSERT_PROFILE = "INSERT INTO PROFILE (LANGPREF, FAVCATEGORY, MYLISTOPT, BANNEROPT, USERID)" +
            " VALUES (?,?,?,?,?)";
    private static final String UPDATE_SIGNON = "UPDATE SIGNON SET PASSWORD =?" +
            " WHERE USERNAME =?";
    private static final String INSERT_SIGNON = "INSERT INTO SIGNON (PASSWORD,USERNAME)" +
            " VALUES (?,?)";
    public static final String INSERT_CART = "INSERT INTO CART (USERID) " +
            /*"ITEMID1,ITEMQUANTITY1," +
            "ITEMID2,ITEMQUANTITY2," +
            "ITEMID3,ITEMQUANTITY3," +
            "ITEMID4,ITEMQUANTITY4," +
            "ITEMID5,ITEMQUANTITY5," +
            "ITEMID6,ITEMQUANTITY6,) " +*/
            "VALUES (?)";
    public static final String INSERT_LOG = "INSERT INTO LOG (USERID) " +
            /*"ITEMID1,ITEMQUANTITY1," +
            "ITEMID2,ITEMQUANTITY2," +
            "ITEMID3,ITEMQUANTITY3," +
            "ITEMID4,ITEMQUANTITY4," +
            "ITEMID5,ITEMQUANTITY5," +
            "ITEMID6,ITEMQUANTITY6,) " +*/
            "VALUES (?)";

    @Override
    public Account getAccountByUsername(String username) {
        Account account = null;
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME);
            preparedStatement.setString(1, username);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                account = new Account();
                account.setUsername(resultSet.getString(1));
                account.setEmail(resultSet.getString(2));
                account.setFirstName(resultSet.getString(3));
                account.setLastName(resultSet.getString(4));
                account.setStatus(resultSet.getString(5));
                account.setAddress1(resultSet.getString(6));
                account.setAddress2(resultSet.getString(7));
                account.setCity(resultSet.getString(8));
                account.setState(resultSet.getString(9));
                account.setZip(resultSet.getString(10));
                account.setCountry(resultSet.getString(11));
                account.setPhone(resultSet.getString(12));
                account.setLanguagePreference(resultSet.getString(13));
                account.setFavouriteCategoryId(resultSet.getString(14));
                account.setListOption(resultSet.getBoolean(15));
                account.setBannerOption(resultSet.getBoolean(16));
                account.setBannerName(resultSet.getString(17));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return account;
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Account accountResult = null;
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                accountResult = new Account();
                accountResult.setUsername(resultSet.getString(1));
                accountResult.setEmail(resultSet.getString(2));
                accountResult.setFirstName(resultSet.getString(3));
                accountResult.setLastName(resultSet.getString(4));
                accountResult.setStatus(resultSet.getString(5));
                accountResult.setAddress1(resultSet.getString(6));
                accountResult.setAddress2(resultSet.getString(7));
                accountResult.setCity(resultSet.getString(8));
                accountResult.setState(resultSet.getString(9));
                accountResult.setZip(resultSet.getString(10));
                accountResult.setCountry(resultSet.getString(11));
                accountResult.setPhone(resultSet.getString(12));
                accountResult.setLanguagePreference(resultSet.getString(13));
                accountResult.setFavouriteCategoryId(resultSet.getString(14));
                accountResult.setListOption(resultSet.getInt(15) == 1);
                accountResult.setBannerOption(resultSet.getInt(16) == 1);
                accountResult.setBannerName(resultSet.getString(17));

            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return accountResult;
    }

    @Override
    public void insertAccount(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement.setString(1, account.getEmail());
            preparedStatement.setString(2, account.getFirstName());
            preparedStatement.setString(3, account.getLastName());
            preparedStatement.setString(4, account.getStatus());
            preparedStatement.setString(5, account.getAddress1());
            preparedStatement.setString(6, account.getAddress2());
            preparedStatement.setString(7, account.getCity());
            preparedStatement.setString(8, account.getState());
            preparedStatement.setString(9, account.getZip());
            preparedStatement.setString(10, account.getCountry());
            preparedStatement.setString(11, account.getPhone());
            preparedStatement.setString(12, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertProfile(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_PROFILE);
            preparedStatement.setString(1, account.getLanguagePreference());
            preparedStatement.setString(2, account.getFavouriteCategoryId());
            preparedStatement.setInt(3, account.isListOption() ? 1 : 0);
            preparedStatement.setInt(4, account.isBannerOption() ? 1 : 0);
            preparedStatement.setString(5, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override    //更新登录信息
    public void insertSignon(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SIGNON);
            preparedStatement.setString(1, account.getPassword());
            preparedStatement.setString(2, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override   //插入新账户的购物车
    public void updateAccount(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ACCOUNT);
            preparedStatement.setString(1, account.getEmail());
            preparedStatement.setString(2, account.getFirstName());
            preparedStatement.setString(3, account.getLastName());
            preparedStatement.setString(4, account.getStatus());
            preparedStatement.setString(5, account.getAddress1());
            preparedStatement.setString(6, account.getAddress2());
            preparedStatement.setString(7, account.getCity());
            preparedStatement.setString(8, account.getState());
            preparedStatement.setString(9, account.getZip());
            preparedStatement.setString(10, account.getCountry());
            preparedStatement.setString(11, account.getPhone());
            preparedStatement.setString(12, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override    //插入新账户的日志
    public void updateProfile(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_PROFILE);
            preparedStatement.setString(1, account.getLanguagePreference());
            preparedStatement.setString(2, account.getFavouriteCategoryId());
            preparedStatement.setString(3, String.valueOf(account.isListOption() ? 1 : 0));
            preparedStatement.setString(4, String.valueOf(account.isBannerOption() ? 1 : 0));
            preparedStatement.setString(5, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignon(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_SIGNON);
            preparedStatement.setString(1, account.getPassword());
            preparedStatement.setString(2, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertAccountCart(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_CART);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertAccountLog(Account account) {
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_LOG);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //测试
//    public static void main(String[] args){
//        AccountDao accountDao = new AccountDaoImpl();
//        Account account = new Account();
//        account.setUsername("j2ee");
//        account.setPassword("j2ee");
//        Account result = accountDao.getAccountByUsernameAndPassword(account);
//        System.out.println("OK");
//    }

}
